package com.neusoft.elm.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.neusoft.elm.dao.CartDao;
import com.neusoft.elm.po.Business;
import com.neusoft.elm.po.Cart;
import com.neusoft.elm.po.Food;
import com.neusoft.elm.util.DBUtil;

public class CartDaoImpl implements CartDao {
	private Connection con = null;
	private PreparedStatement pst = null;
	private ResultSet rs = null;

	@Override
	public List<Cart> listCart(Cart cart) throws Exception {
		List<Cart> list = new ArrayList<>();
		StringBuffer sql = new StringBuffer();
		sql.append(" select c.*, ");
		sql.append("        f.foodId ffoodId, f.foodName ffoodName, f.foodExplain ffoodExplain, ");
		sql.append(
				"        f.foodImg ffoodImg, f.foodPrice ffoodPrice, f.businessId fbusinessId, f.remarks fremarks, ");
		sql.append(
				"        b.businessId bbusinessId, b.businessName bbusinessName, b.businessAddress bbusinessAddress, ");
		sql.append("        b.businessExplain bbusinessExplain, b.businessImg bbusinessImg, ");
		sql.append("        b.orderTypeId borderTypeId, b.starPrice bstarPrice, b.deliveryPrice bdeliveryPrice");
		sql.append(" from (cart c left join food f on c.foodId=f.foodId) ");
		sql.append("      left join business b on c.businessId=b.businessId ");
		sql.append(" where 1=1 ");

		if (cart.getBusinessId() != null) {
			sql.append(" and c.businessId=" + cart.getBusinessId());
		}
		if (cart.getUserId() != null) {
			sql.append(" and c.userId='" + cart.getUserId() + "'");
		}
		sql.append(" order by c.updateTime desc");

		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql.toString());
			rs = pst.executeQuery();
			while (rs.next()) {
				Cart c = new Cart();
				c.setCartId(rs.getInt("cartId"));
				c.setFoodId(rs.getInt("foodId"));
				c.setBusinessId(rs.getInt("businessId"));
				c.setUserId(rs.getString("userId"));
				c.setQuantity(rs.getInt("quantity"));
				c.setCreateTime(rs.getTimestamp("createTime"));
				c.setUpdateTime(rs.getTimestamp("updateTime"));
				c.setDeviceId(rs.getString("deviceId"));
				c.setSyncStatus(rs.getInt("syncStatus"));
				c.setSessionId(rs.getString("sessionId"));

				// Set Food and Business objects as before
				Food f = new Food();
				f.setFoodId(rs.getInt("ffoodId"));
				f.setFoodName(rs.getString("ffoodName"));
				f.setFoodExplain(rs.getString("ffoodExplain"));
				f.setFoodImg(rs.getString("ffoodImg"));
				f.setFoodPrice(rs.getDouble("ffoodPrice"));
				f.setBusinessId(rs.getInt("fbusinessId"));
				f.setRemarks(rs.getString("fremarks"));
				c.setFood(f);

				Business b = new Business();
				b.setBusinessId(rs.getInt("bbusinessId"));
				b.setBusinessName(rs.getString("bbusinessName"));
				b.setBusinessAddress(rs.getString("bbusinessAddress"));
				b.setBusinessExplain(rs.getString("bbusinessExplain"));
				b.setBusinessImg(rs.getString("bbusinessImg"));
				b.setOrderTypeId(rs.getInt("borderTypeId"));
				b.setStarPrice(rs.getDouble("bstarPrice"));
				b.setDeliveryPrice(rs.getDouble("bdeliveryPrice"));
				c.setBusiness(b);

				list.add(c);
			}
		} finally {
			DBUtil.close(rs, pst);
		}
		return list;
	}

	@Override
	public int saveCart(Cart cart) throws Exception {
		int result = 0;
		String sql = "insert into cart values(null,?,?,?,?,now(),now(),?,0,?)";
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setInt(1, cart.getFoodId());
			pst.setInt(2, cart.getBusinessId());
			pst.setString(3, cart.getUserId());
			pst.setInt(4, cart.getQuantity() != null ? cart.getQuantity() : 1);
			pst.setString(5, cart.getDeviceId());
			pst.setString(6, cart.getSessionId());
			result = pst.executeUpdate();
		} finally {
			DBUtil.close(pst);
		}
		return result;
	}

	@Override
	public int saveOrUpdateCart(Cart cart) throws Exception {
		int result = 0;
		// 先检查是否存在相同的购物车项
		String checkSql = "select cartId, quantity from cart where foodId=? and businessId=? and userId=?";
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(checkSql);
			pst.setInt(1, cart.getFoodId());
			pst.setInt(2, cart.getBusinessId());
			pst.setString(3, cart.getUserId());
			rs = pst.executeQuery();

			if (rs.next()) {
				// 更新现有项目
				int existingQuantity = rs.getInt("quantity");
				int newQuantity = existingQuantity + (cart.getQuantity() != null ? cart.getQuantity() : 1);
				String updateSql = "update cart set quantity=?, updateTime=now(), deviceId=?, syncStatus=0, sessionId=? where cartId=?";
				pst = con.prepareStatement(updateSql);
				pst.setInt(1, newQuantity);
				pst.setString(2, cart.getDeviceId());
				pst.setString(3, cart.getSessionId());
				pst.setInt(4, rs.getInt("cartId"));
				result = pst.executeUpdate();
			} else {
				// 插入新项目
				result = saveCart(cart);
			}
		} finally {
			DBUtil.close(rs, pst);
		}
		return result;
	}

	@Override
	public List<Cart> getUnsyncedCarts(String userId) throws Exception {
		List<Cart> list = new ArrayList<>();
		String sql = "select * from cart where userId=? and syncStatus=0 order by updateTime desc";
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, userId);
			rs = pst.executeQuery();
			while (rs.next()) {
				Cart cart = new Cart();
				cart.setCartId(rs.getInt("cartId"));
				cart.setFoodId(rs.getInt("foodId"));
				cart.setBusinessId(rs.getInt("businessId"));
				cart.setUserId(rs.getString("userId"));
				cart.setQuantity(rs.getInt("quantity"));
				cart.setCreateTime(rs.getTimestamp("createTime"));
				cart.setUpdateTime(rs.getTimestamp("updateTime"));
				cart.setDeviceId(rs.getString("deviceId"));
				cart.setSyncStatus(rs.getInt("syncStatus"));
				cart.setSessionId(rs.getString("sessionId"));
				list.add(cart);
			}
		} finally {
			DBUtil.close(rs, pst);
		}
		return list;
	}

	@Override
	public int markCartsSynced(List<Integer> cartIds) throws Exception {
		if (cartIds == null || cartIds.isEmpty())
			return 0;

		StringBuilder sql = new StringBuilder("update cart set syncStatus=1 where cartId in (");
		for (int i = 0; i < cartIds.size(); i++) {
			if (i > 0)
				sql.append(",");
			sql.append("?");
		}
		sql.append(")");

		int result = 0;
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql.toString());
			for (int i = 0; i < cartIds.size(); i++) {
				pst.setInt(i + 1, cartIds.get(i));
			}
			result = pst.executeUpdate();
		} finally {
			DBUtil.close(pst);
		}
		return result;
	}

	@Override
	public List<Cart> getCartsByDevice(String userId, String deviceId) throws Exception {
		List<Cart> list = new ArrayList<>();
		String sql = "select * from cart where userId=? and deviceId=? order by updateTime desc";
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, userId);
			pst.setString(2, deviceId);
			rs = pst.executeQuery();
			while (rs.next()) {
				Cart cart = new Cart();
				cart.setCartId(rs.getInt("cartId"));
				cart.setFoodId(rs.getInt("foodId"));
				cart.setBusinessId(rs.getInt("businessId"));
				cart.setUserId(rs.getString("userId"));
				cart.setQuantity(rs.getInt("quantity"));
				cart.setCreateTime(rs.getTimestamp("createTime"));
				cart.setUpdateTime(rs.getTimestamp("updateTime"));
				cart.setDeviceId(rs.getString("deviceId"));
				cart.setSyncStatus(rs.getInt("syncStatus"));
				cart.setSessionId(rs.getString("sessionId"));
				list.add(cart);
			}
		} finally {
			DBUtil.close(rs, pst);
		}
		return list;
	}

	@Override
	public int clearUserCart(String userId) throws Exception {
		int result = 0;
		String sql = "delete from cart where userId=?";
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, userId);
			result = pst.executeUpdate();
		} finally {
			DBUtil.close(pst);
		}
		return result;
	}

	@Override
	public int removeCart(Cart cart) throws Exception {
		int result = 0;
		StringBuffer sql = new StringBuffer("delete from cart where 1=1 ");
		if (cart.getFoodId() != null) {
			sql.append(" and foodId=" + cart.getFoodId());
		}
		if (cart.getBusinessId() != null) {
			sql.append(" and businessId=" + cart.getBusinessId());
		}
		if (cart.getUserId() != null) {
			sql.append(" and userId='" + cart.getUserId() + "'");
		}
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql.toString());
			result = pst.executeUpdate();
		} finally {
			DBUtil.close(pst);
		}
		return result;
	}

	@Override
	public int updateCart(Cart cart) throws Exception {
		int result = 0;
		String sql = "update cart set quantity=?, updateTime=now(), syncStatus=0 where foodId=? and businessId=? and userId=?";
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setInt(1, cart.getQuantity());
			pst.setInt(2, cart.getFoodId());
			pst.setInt(3, cart.getBusinessId());
			pst.setString(4, cart.getUserId());
			result = pst.executeUpdate();
		} finally {
			DBUtil.close(pst);
		}
		return result;
	}
}